use std::collections::HashMap;

use sea_orm::{ConnectionTrait, DbConn, QueryResult};
use sea_query::{Alias, Asterisk, Expr, Order as SeaOrder, OrderedStatement};

use crate::{
    error::Result,
    model::{Filter, GenTable, Order},
    JsonMap,
};

pub struct Query;

impl Query {
    pub async fn select<'a>(table: &'a GenTable) -> sea_query::SelectStatement {
        let mut sql = sea_query::Query::select();
        sql.from(table);
        sql
    }

    pub async fn count_of(
        db: &DbConn,
        table: &GenTable,
        filters: Option<&Vec<Filter>>,
    ) -> Result<u32> {
        let mut sql = sea_query::Query::select();
        sql.from(table);
        sql.expr(Expr::col(Asterisk).count());
        if let Some(filters) = filters {
            filters.iter().for_each(|f| {
                sql.cond_where(f.get_cond());
            });
        }
        let count_stat = db.get_database_backend().build(&sql);
        db.query_one(count_stat)
            .await
            .map(|f| match f {
                Some(r) => r.try_get_by_index::<u32>(0).unwrap_or(0),
                _ => 0,
            })
            .map_err(Into::into)
    }

    fn get_query_sql<'a>(
        table: &'a GenTable,
        columns: Option<Vec<String>>,
        filters: &'a Vec<Filter>,
    ) -> Result<(
        sea_query::SelectStatement,
        HashMap<String, &'a crate::model::GenColumn>,
    )> {
        let mut sql = sea_query::Query::select();
        sql.from(table);
        let cols = table.coloumns_map(columns)?;
        for col in cols.iter() {
            sql.expr_as(Into::<Expr>::into(*col.1), Alias::new(col.0));
        }

        filters.iter().for_each(|f| {
            sql.cond_where(f.get_cond());
        });
        Ok((sql, cols))
    }

    fn map_value(
        query_result: &QueryResult,
        columns: &HashMap<String, &crate::model::GenColumn>,
    ) -> Result<JsonMap> {
        let mut map = serde_json::Map::new();
        for f in columns {
            map.insert(f.0.clone(), f.1.try_get_value(query_result)?);
        }
        Ok(map)
    }

    pub async fn list_from(
        db: &DbConn,
        table: &GenTable,
        columns: Option<Vec<String>>,
        filters: Vec<Filter>,
        limit: u64,
        offset: u64,
        orders: Vec<Order>,
    ) -> Result<Vec<JsonMap>> {
        let (mut sql, cols) = self::Query::get_query_sql(table, columns, &filters)?;

        sql.limit(limit);
        sql.offset(offset);

        orders.iter().for_each(|f| {
            sql.order_by(
                Alias::new(&f.0),
                if f.1 { SeaOrder::Asc } else { SeaOrder::Desc },
            );
        });

        let statwment = db.get_database_backend().build(&sql);
        let mut result = vec![];
        for query_result in db.query_all(statwment).await?.iter() {
            result.push(Self::map_value(query_result, &cols)?);
        }
        Ok(result)
    }

    pub async fn get_from(
        db: &DbConn,
        table: &GenTable,
        columns: Option<Vec<String>>,
        filters: Vec<Filter>,
    ) -> Result<Option<JsonMap>> {
        let (sql, cols) = self::Query::get_query_sql(table, columns, &filters)?;
        let statwment = db.get_database_backend().build(&sql);
        db.query_one(statwment)
            .await?
            .map(|r| Self::map_value(&r, &cols))
            .transpose()
    }
}
